import os
if os.getenv('AG_ENV_PYTHON_PATH') is None:
    ROOT_DIR = os.path.dirname(os.path.abspath(__file__)) + os.sep
else:
    ROOT_DIR = os.getcwd() + os.sep
    # ROOT_DIR = WORK_DIR + "ag_art" + os.sep
parent_dir = os.path.dirname(ROOT_DIR)
import importlib.util
abstractjob_path = parent_dir+'/SKO/AbstractDPJob.py'
spec = importlib.util.spec_from_file_location('AbstractDPJob', abstractjob_path)
abstractjob_module = importlib.util.module_from_spec(spec)
spec.loader.exec_module(abstractjob_module)
AbstractDPJob = abstractjob_module.AbstractDPJob

# from SKO.AbstractDPJob import AbstractDPJob
import sys, datetime, json
import pandas as pd
import numpy as np
from numpy import array
import cvxpy as cp
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
import os

class JianyanJob(AbstractDPJob):


    def __init__(self,
                 p_tmpl_no=None, p_cog_dest=None):

        super(JianyanJob, self).__init__()
        self.tmpl_no = p_tmpl_no
        self.cog_dest = p_cog_dest
        pass


    def execute(self):
        return self.do_execute()


    def do_execute(self):

        super(JianyanJob, self).do_execute()

        tmpl_no = self.tmpl_no
        cog_dest = self.cog_dest
        # 数据库配置写死
        DB_HOST_MPP_DB2 = '10.70.48.41'
        DB_PORT_MPP_DB2 = 50021
        DB_DBNAME_MPP_DB2 = 'BGBDPROD'
        DB_USER_MPP_DB2 = 'g0mazzai'
        DB_PASSWORD_MPP_DB2 = 'g0mazzaibg00'

        # 数据库连接函数写死
        def getConnectionDb2(host, port, dbname, user, password):
            # conn = pg.connect(host=host, port=port, dbname=dbname, user=user, password=password)
            engine = create_engine('ibm_db_sa://' + user + ':' + password + '@' + host + ':' + str(port) + '/' + dbname,
                                   encoding="utf-8", poolclass=NullPool)
            return engine.connect()

        db_conn_mpp = getConnectionDb2(DB_HOST_MPP_DB2,
                                       DB_PORT_MPP_DB2,
                                       DB_DBNAME_MPP_DB2,
                                       DB_USER_MPP_DB2,
                                       DB_PASSWORD_MPP_DB2)

        max_constant = 1000000
        # 读数据
        sql = " select VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE,UNIT_PRICE,INIT_INV,INV_WT,MERGE_FLAG,PLAN_WT " \
              " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_PRICE " \
              " where TMPL_NO ='%s' and DATA_TYPE='MX' and VAR !='其他参数' " % (tmpl_no)
        data_meizhong = pd.read_sql_query(sql, con=db_conn_mpp)
        data_meizhong.columns = data_meizhong.columns.str.upper()
        sql = " select VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE,MIN_VALUE,MAX_VALUE,FLAG " \
              " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_RATIO " \
              " where TMPL_NO ='%s' and DATA_TYPE='MX' " % (tmpl_no)
        data_bili = pd.read_sql_query(sql, con=db_conn_mpp)
        data_bili.columns = data_bili.columns.str.upper()
        sql = " select VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE,ASH,COKE_VM,S " \
              " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_IND_INFO " \
              " where TMPL_NO ='%s' and DATA_TYPE='MX' " % (tmpl_no)
        data_meizhi = pd.read_sql_query(sql, con=db_conn_mpp)
        data_meizhi.columns = data_meizhi.columns.str.upper()
        sql = " select BIG_VAR,FLAG,ASH,COKE_VM,S " \
              " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_INDLIMIT " \
              " where TMPL_NO ='%s' and DATA_TYPE='MX' " % (tmpl_no)
        data_shangxiaxian = pd.read_sql_query(sql, con=db_conn_mpp)
        data_shangxiaxian.columns = data_shangxiaxian.columns.str.upper()
        # 去除资源量为0的品种，减少变量
        # 月初库存
        data_meizhong.INIT_INV.fillna(0, inplace=True)
        # 可供资源
        data_meizhong.INV_WT.fillna(0, inplace=True)
        # 资源量
        # data_meizhong.RESOURCE_WT.fillna(0, inplace=True)
        # 计划使用量
        data_meizhong.PLAN_WT.fillna(0, inplace=True)

        def __cal_resource_wt(x):
            rst = x.INIT_INV + x.INV_WT
            return rst
        data_meizhong['RESOURCE_WT'] = data_meizhong.apply(lambda x: __cal_resource_wt(x), axis=1)

        def __cal_max_wt(x):
            rst = max(x.RESOURCE_WT, x.PLAN_WT)
            return rst

        data_meizhong['MAX_WT'] = data_meizhong.apply(lambda x: __cal_max_wt(x), axis=1)
        data_meizhong = data_meizhong[(data_meizhong['MAX_WT'] > 0)]
        data_meizhong = data_meizhong.reset_index(drop=True)
        # ganmeiliang = data_meizhong['PLAN_WT'].sum()
        peihemei_wt = data_meizhong['PLAN_WT'].sum()

        # 对煤种生成唯一编码mark
        # 维度上只考虑VAR性状，SOURCE来源
        # 写死维度种类对应关系？
        data_mark = data_meizhong.copy()

        def __cal_rank_xingzhuang(x):
            if x.VAR == '主焦':
                rst = 1
            elif x.VAR == '肥煤':
                rst = 2
            elif x.VAR == '1/3焦':
                rst = 3
            elif x.VAR == '气煤':
                rst = 4
            elif x.VAR == '瘦煤':
                rst = 5
            else:
                rst = 6
            return rst

        data_mark['xingzhuang_rank'] = data_mark.apply(lambda x: __cal_rank_xingzhuang(x), axis=1)

        def __cal_rank_laiyuan(x):
            if x.SOURCE == '进口':
                rst = 1
            elif x.SOURCE == '大矿长协':
                rst = 2
            elif x.SOURCE == '大矿市场':
                rst = 3
            elif x.SOURCE == '地方矿':
                rst = 4
            else:
                rst = 5
            return rst

        data_mark['laiyuan_rank'] = data_mark.apply(lambda x: __cal_rank_laiyuan(x), axis=1)

        def __cal_rank_group(x):
            rst = str(x.xingzhuang_rank) + '_' + str(x.laiyuan_rank)
            return rst

        data_mark['group'] = data_mark.apply(lambda x: __cal_rank_group(x), axis=1)
        data_mark = data_mark.reset_index(drop=False)
        data_mark.rename(columns={'index': 'index_old'}, inplace=True)

        data_mark['rank0'] = data_mark['index_old'].groupby(data_mark['group']).rank()
        data_mark['pinming_rank'] = data_mark['rank0'].astype(int)

        def __cal_rank_mark(x):
            rst = str(x.group) + '_' + str(x.pinming_rank)
            return rst

        data_mark['mark'] = data_mark.apply(lambda x: __cal_rank_mark(x), axis=1)
        data_mark.drop(['rank0'], axis=1, inplace=True)
        # 统计个数，做个var_df以便后续对应
        var_list = ['主焦', '肥煤', '1/3焦', '气煤', '瘦煤']
        source_list = ['进口', '大矿长协', '大矿市场', '地方矿']
        var_df = pd.DataFrame(columns=['VAR', 'SOURCE', 'group', 'count'])
        dict = {}
        for i in range(1, len(var_list) + 1):
            for j in range(1, len(source_list) + 1):
                df = data_mark[
                    (data_mark['VAR'] == var_list[i - 1]) & (data_mark['SOURCE'] == source_list[j - 1])]
                row_count = df.shape[0]
                # print("行数：", row_count)
                dict['VAR'] = var_list[i - 1]
                dict['SOURCE'] = source_list[j - 1]
                dict['group'] = str(i) + '_' + str(j)
                dict['count'] = row_count
                new_row = pd.Series(dict)
                var_df = var_df.append(new_row, ignore_index=True)
        # 生成动态参数
        # 资源量，分配比例上下限
        # 资源量，按照品名的
        data_ziyuanliang_1 = data_mark[
            ['PROD_CODE', 'INIT_INV', 'INV_WT', 'RESOURCE_WT', 'MERGE_FLAG', 'MAX_WT', 'mark']]
        data_ziyuanliang_1.RESOURCE_WT.fillna(0, inplace=True)
        for index, row in data_ziyuanliang_1.iterrows():
            exec('ziyuanliang_{} ={}'.format(row['mark'], row['MAX_WT']))
        # 资源量，合并单元格的
        merge_num = data_mark["MERGE_FLAG"].max()
        print(merge_num)
        for i in range(1, int(merge_num) + 1):
            print(i)
            data_ziyuanliang_2 = data_ziyuanliang_1[(data_ziyuanliang_1['MERGE_FLAG'] == i)]
            data_ziyuanliang_2 = data_ziyuanliang_2.reset_index(drop=True)
            tmp_merge_ziyuanliang = data_ziyuanliang_2.loc[0]['INV_WT']
            exec('merge_ziyuanliang_{} ={}'.format(i, tmp_merge_ziyuanliang))
        # 分配比例上下限，按照品名的
        data_bili.rename(columns={'MAX_VALUE': 'UL'}, inplace=True)
        data_bili.rename(columns={'MIN_VALUE': 'LL'}, inplace=True)
        data_bili.UL.fillna(100, inplace=True)
        data_bili.LL.fillna(0, inplace=True)
        data_bili_1 = data_bili[(data_bili['FLAG'] == '品名')]
        data_bili_1 = data_bili_1.reset_index(drop=True)
        data_bili_1 = data_bili_1[['PROD_CODE', 'UL', 'LL']]
        v = ['PROD_CODE']
        df3 = pd.merge(data_mark, data_bili_1, on=v, how='left')
        for index, row in df3.iterrows():
            exec('ul_{} ={}'.format(row['mark'], row['UL']))
            exec('ll_{} ={}'.format(row['mark'], row['LL']))
        # 分配比例上下限，按照性状的
        for i in range(0, len(var_list)):
            print(var_list[i])
            tmp_var = var_list[i]
            data_bili_2 = data_bili[(data_bili['FLAG'] == '性状') & (data_bili['VAR'] == tmp_var)]
            data_bili_2 = data_bili_2.reset_index(drop=True)
            tmp_ul = data_bili_2.loc[0]['UL']
            tmp_ll = data_bili_2.loc[0]['LL']
            exec('ul_var_{} ={}'.format(i + 1, tmp_ul))
            exec('ll_var_{} ={}'.format(i + 1, tmp_ll))
        # 分配比例上下限，按照来源的
        for i in range(0, len(source_list)):
            print(source_list[i])
            tmp_source = source_list[i]
            data_bili_3 = data_bili[(data_bili['FLAG'] == '来源') & (data_bili['SOURCE'] == tmp_source)]
            data_bili_3 = data_bili_3.reset_index(drop=True)
            tmp_ul = data_bili_3.loc[0]['UL']
            tmp_ll = data_bili_3.loc[0]['LL']
            exec('ul_source_{} ={}'.format(i + 1, tmp_ul))
            exec('ll_source_{} ={}'.format(i + 1, tmp_ll))
        # 分配比例上下限，按照性状来源的

        # var_df_1 = var_df[(var_df['count'] > 0)]
        # var_df_1 = var_df_1.reset_index(drop=True)

        var_df1 = var_df.copy()

        def __cal_new_var(x):
            rst = str(x.VAR) + '_' + str(x.SOURCE)
            return rst

        var_df1['NEW_VAR'] = var_df1.apply(lambda x: __cal_new_var(x), axis=1)

        data_bili_4 = data_bili[(data_bili['FLAG'] == '性状来源')]
        data_bili_4 = data_bili_4.reset_index(drop=True)

        def __cal_new_var(x):
            rst = str(x.VAR) + '_' + str(x.SOURCE)
            return rst

        data_bili_4['NEW_VAR'] = data_bili_4.apply(lambda x: __cal_new_var(x), axis=1)
        data_bili_4 = data_bili_4[['NEW_VAR', 'UL', 'LL']]
        v = ['NEW_VAR']
        df3 = pd.merge(var_df1, data_bili_4, on=v, how='left')
        for index, row in df3.iterrows():
            exec('ul_group_{} ={}'.format(row['group'], row['UL']))
            exec('ll_group_{} ={}'.format(row['group'], row['LL']))
        # 煤质
        data_meizhi.rename(columns={'ASH': 'hui'}, inplace=True)
        data_meizhi.rename(columns={'COKE_VM': 'huifa'}, inplace=True)
        data_meizhi.rename(columns={'S': 'liu'}, inplace=True)
        data_meizhi_1 = data_meizhi[['PROD_CODE', 'hui', 'huifa', 'liu']]
        v = ['PROD_CODE']
        df3 = pd.merge(data_mark, data_meizhi_1, on=v, how='left')
        df3.fillna(max_constant, inplace=True)
        for index, row in df3.iterrows():
            exec('hui_{} ={}'.format(row['mark'], row['hui']))
            exec('huifa_{} ={}'.format(row['mark'], row['huifa']))
            exec('liu_{} ={}'.format(row['mark'], row['liu']))
        # 煤质上下限
        data_shangxiaxian_1 = data_shangxiaxian[
            (data_shangxiaxian['FLAG'] == '上限') & (data_shangxiaxian['BIG_VAR'] == '炼焦煤')]
        data_shangxiaxian_1 = data_shangxiaxian_1.reset_index(drop=True)
        data_shangxiaxian_1.fillna(max_constant, inplace=True)
        lianjiao_hui_ul = data_shangxiaxian_1.loc[0]['ASH']
        lianjiao_huifa_ul = data_shangxiaxian_1.loc[0]['COKE_VM']
        lianjiao_liu_ul = data_shangxiaxian_1.loc[0]['S']
        data_shangxiaxian_2 = data_shangxiaxian[
            (data_shangxiaxian['FLAG'] == '下限') & (data_shangxiaxian['BIG_VAR'] == '炼焦煤')]
        data_shangxiaxian_2 = data_shangxiaxian_2.reset_index(drop=True)
        data_shangxiaxian_2.fillna(0, inplace=True)
        lianjiao_hui_ll = data_shangxiaxian_2.loc[0]['ASH']
        lianjiao_huifa_ll = data_shangxiaxian_2.loc[0]['COKE_VM']
        lianjiao_liu_ll = data_shangxiaxian_2.loc[0]['S']

        sql = " select COKEYR_CONST,COG_GEN_CONST,SINTER_CRSCOKE_RATIO, " \
              " COKING_METCOKRAT,PCOKE_COKEPOWD_RATE,ELECOUT,COAL_USE, " \
              " COKE_OUTPUT,POWOUTSRC_PRICE,NG_PRICE,THMCOAL_PRICE " \
              " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_COEF " \
              " where TMPL_NO ='%s' and DATA_TYPE='CS' " % (tmpl_no)
        data_canshu = pd.read_sql_query(sql, con=db_conn_mpp)
        data_canshu.columns = data_canshu.columns.str.upper()
        chengjiaolv_constant = data_canshu.loc[0]['COKEYR_CONST']
        cogfasheng_constant = data_canshu.loc[0]['COG_GEN_CONST']
        cujiaolv = data_canshu.loc[0]['SINTER_CRSCOKE_RATIO']
        yejinjiaolv = data_canshu.loc[0]['COKING_METCOKRAT']
        waigoujiaofenlv = data_canshu.loc[0]['PCOKE_COKEPOWD_RATE']
        cogfadian = data_canshu.loc[0]['ELECOUT']
        fadianmeihao = data_canshu.loc[0]['COAL_USE']
        jiaotanchanliang = data_canshu.loc[0]['COKE_OUTPUT']
        price_waigoudian = data_canshu.loc[0]['POWOUTSRC_PRICE']
        price_tianranqi = data_canshu.loc[0]['NG_PRICE']
        price_fadianmei = data_canshu.loc[0]['THMCOAL_PRICE']
        sql = " select VAR,PROD_DSCR,UNIT_PRICE " \
              " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_PRICE " \
              " where TMPL_NO ='%s' and DATA_TYPE='CS' and VAR='其他参数' " % (tmpl_no)
        data_sourceprice = pd.read_sql_query(sql, con=db_conn_mpp)
        data_sourceprice.columns = data_sourceprice.columns.str.upper()
        data_other1 = data_sourceprice[data_sourceprice['PROD_DSCR'] == '外购焦炭']
        data_other1 = data_other1.reset_index(drop=True)
        price_waigoujiaotan = data_other1.loc[0]['UNIT_PRICE']
        data_other2 = data_sourceprice[data_sourceprice['PROD_DSCR'] == '烧结燃料']
        data_other2 = data_other2.reset_index(drop=True)
        price_shaojieranliao = data_other2.loc[0]['UNIT_PRICE']
        data_other3 = data_sourceprice[data_sourceprice['PROD_DSCR'] == 'COG']
        data_other3 = data_other3.reset_index(drop=True)
        price_cog = data_other3.loc[0]['UNIT_PRICE']
        # 其他配置的参数
        # 价格
        price_waigoujiaotan = price_waigoujiaotan
        price_waigoujiaofen = price_shaojieranliao
        # price_tianranqi = 2.7
        # price_waigoudian = 0.72
        # price_fadianmei = 807.0
        # price_cog = 1.39
        # 一些参数
        # cujiaolv = 0.115
        # yejinjiaolv = 0.835
        waigoujiao_yejinjiaolv = 1 - waigoujiaofenlv
        waigoujiao_cujiaolv = waigoujiaofenlv
        COGfadian = cogfadian
        # fadianmeihao = 420
        # ganmeiliang = 67
        # peihemei_wt = ganmeiliang
        sql = " select SCHEME_NAME,COKING_PCC_RATIO,COKING_FATCOAL_RATIO,COKING_GFCOAL_RATIO, " \
              " COKING_GASCOAL_RATIO,COKING_LEANCOAL_RATIO,UNIT_PRICE,ASH,COKE_VM,S, " \
              " COKING_COKEYR,COKE_OUTPUT,COG_GEN " \
              " from BG00MAZZAI.T_ADS_FACT_YLMX_COKE_MODEL_S " \
              " where TMPL_NO ='%s' and DATA_TYPE='CS' " % (tmpl_no)
        data_jieguotongji = pd.read_sql_query(sql, con=db_conn_mpp)
        data_jieguotongji.columns = data_jieguotongji.columns.str.upper()
        data_month = data_jieguotongji[data_jieguotongji['SCHEME_NAME'] == '月预算']
        data_month = data_month.reset_index(drop=True)
        month_unit_price = data_month.loc[0]['UNIT_PRICE']
        month_ash = data_month.loc[0]['ASH']
        month_coke_vm = data_month.loc[0]['COKE_VM']
        month_s = data_month.loc[0]['S']
        month_chengjiaolv = data_month.loc[0]['COKING_COKEYR']
        month_jiaotanchanliang = data_month.loc[0]['COKE_OUTPUT']
        month_cogfasheng = data_month.loc[0]['COG_GEN']
        ganmeiliang = month_jiaotanchanliang / month_chengjiaolv * 100

        # 构建系数矩阵
        coef_df = data_mark[['mark']]
        mark_list = coef_df['mark'].to_list()
        array_len = len(mark_list)
        # 构建所有参数对应的约束df
        canshu_df = pd.DataFrame(
            columns=['index', 'coef_name', 'step', 'mark', 'vvar', 'source', 'group', 'meizhi', 'merge_flag'])
        dict_canshu = {}
        j_start = 0
        # 炼焦煤总量,暂认为干煤量
        # 等式约束
        import numpy as np
        from numpy import array
        y0 = array([[0] * array_len], dtype=float)
        y0[0, 0] = 1
        mark_tmp_0 = mark_list[0]
        ldict100 = {}
        exec('m0 = array([ziyuanliang_{}])'.format(mark_tmp_0), locals(), ldict100)
        m0 = ldict100["m0"]
        j_start = j_start + 1
        e2 = array([[1] * array_len], dtype=float)
        f2 = array([peihemei_wt])
        # 不等式约束
        # 资源量
        # 品名资源量
        for j in range(j_start, j_start + int(array_len)-1):
            exec('y{} = array([[0] * array_len],dtype=float)'.format(j))
            exec('y{}[0,j-j_start+1] = 1'.format(j))
            mark_tmp = mark_list[j]
            exec('m{} = array([ziyuanliang_{}])'.format(j, mark_tmp))
            dict_canshu['index'] = j
            dict_canshu['coef_name'] = '资源量'
            if mark_tmp[0] == '1':
                buzhou_tmp = '主焦'
            elif mark_tmp[0] == '2':
                buzhou_tmp = '肥煤'
            elif mark_tmp[0] == '3':
                buzhou_tmp = '1/3焦'
            elif mark_tmp[0] == '4':
                buzhou_tmp = '气煤'
            elif mark_tmp[0] == '5':
                buzhou_tmp = '瘦煤'
            dict_canshu['step'] = buzhou_tmp
            dict_canshu['mark'] = mark_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
        j_start = j_start + int(array_len)-1
        # 合并单元格资源量

        for j in range(j_start, int(merge_num) + j_start):
            print(j)
            exec('y{} = array([[0] * array_len],dtype=float)'.format(j))
            data_mark_merge = data_mark[(data_mark['MERGE_FLAG'] == j - j_start + 1)]
            data_mark_merge = data_mark_merge.reset_index(drop=True)
            var_tmp = data_mark_merge.loc[0]['VAR']

            data_mark_merge['if_merge'] = 1
            yuechukucun_sum = data_mark_merge['INIT_INV'].sum()
            data_mark_merge = data_mark_merge[['mark', 'if_merge']]
            v = ['mark']
            df3_merge = pd.merge(coef_df, data_mark_merge, on=v, how='left')
            df3_merge.if_merge.fillna(0, inplace=True)
            for index, row in df3_merge.iterrows():
                exec('y{}[0,index] = row["if_merge"]'.format(j))
            exec('m{} = array([merge_ziyuanliang_{}+yuechukucun_sum])'.format(j, j - j_start + 1))

            dict_canshu['index'] = j
            dict_canshu['coef_name'] = '合并可供资源量'
            dict_canshu['step'] = '合并可供资源量'
            dict_canshu['merge_flag'] = j - j_start + 1
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}

        j_start = j_start + int(merge_num)
        # 分配比例
        # 性状分配比例
        ldict1 = {}
        ldict2 = {}
        for i in range(1, len(var_list) + 1):
            var_tmp = str(i)
            # 上限
            coef_df1 = coef_df.copy()
            exec('true_value = 100 - ul_var_{}'.format(var_tmp), locals(), ldict1)
            true_value = ldict1["true_value"]
            # print(true_value)
            exec('false_value = - ul_var_{}'.format(var_tmp), locals(), ldict1)
            false_value = ldict1["false_value"]

            # print(false_value)
            def __cal_coef(x):
                if x.mark[0] == var_tmp:
                    rst = true_value
                else:
                    rst = false_value
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '性状比例上限'
            dict_canshu['step'] = '维度比例上下限'
            dict_canshu['vvar'] = var_list[i - 1]
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
            # 下限
            coef_df1 = coef_df.copy()
            exec('true_value = ll_var_{} - 100'.format(var_tmp), locals(), ldict2)
            true_value = ldict2["true_value"]
            # print(true_value)
            exec('false_value = ll_var_{}'.format(var_tmp), locals(), ldict2)
            false_value = ldict2["false_value"]

            # print(false_value)
            def __cal_coef(x):
                if x.mark[0] == var_tmp:
                    rst = true_value
                else:
                    rst = false_value
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '性状比例下限'
            dict_canshu['step'] = '维度比例上下限'
            dict_canshu['vvar'] = var_list[i - 1]
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
        # 来源分配比例
        ldict3 = {}
        ldict4 = {}
        for i in range(1, len(source_list) + 1):
            source_tmp = str(i)
            # 上限
            coef_df1 = coef_df.copy()
            exec('true_value = 100 - ul_source_{}'.format(source_tmp), locals(), ldict3)
            true_value = ldict3["true_value"]
            # print(true_value)
            exec('false_value = - ul_source_{}'.format(source_tmp), locals(), ldict3)
            false_value = ldict3["false_value"]

            # print(false_value)
            def __cal_coef(x):
                if x.mark[2] == source_tmp:
                    rst = true_value
                else:
                    rst = false_value
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '来源比例上限'
            dict_canshu['step'] = '维度比例上下限'
            dict_canshu['source'] = source_list[i - 1]
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
            # 下限
            coef_df1 = coef_df.copy()
            exec('true_value = ll_source_{} - 100'.format(source_tmp), locals(), ldict4)
            true_value = ldict4["true_value"]
            # print(true_value)
            exec('false_value = ll_source_{}'.format(source_tmp), locals(), ldict4)
            false_value = ldict4["false_value"]

            # print(false_value)
            def __cal_coef(x):
                if x.mark[2] == source_tmp:
                    rst = true_value
                else:
                    rst = false_value
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '来源比例下限'
            dict_canshu['step'] = '维度比例上下限'
            dict_canshu['source'] = source_list[i - 1]
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
        # 性状来源分配比例
        group_list = var_df['group'].to_list()
        ldict5 = {}
        ldict6 = {}
        for group_tmp in group_list:
            # 上限
            coef_df1 = coef_df.copy()
            exec('true_value = 100 - ul_group_{}'.format(group_tmp), locals(), ldict5)
            true_value = ldict5["true_value"]
            # print(true_value)
            exec('false_value = - ul_group_{}'.format(group_tmp), locals(), ldict5)
            false_value = ldict5["false_value"]

            # print(false_value)
            def __cal_coef(x):
                if x.mark[0:3] == group_tmp:
                    rst = true_value
                else:
                    rst = false_value
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '性状来源比例上限'
            dict_canshu['step'] = '维度比例上下限'
            if group_tmp[0] == '1':
                var_tmp = '主焦'
            elif group_tmp[0] == '2':
                var_tmp = '肥煤'
            elif group_tmp[0] == '3':
                var_tmp = '1/3焦'
            elif group_tmp[0] == '4':
                var_tmp = '气煤'
            elif group_tmp[0] == '5':
                var_tmp = '瘦煤'
            dict_canshu['vvar'] = var_tmp
            if group_tmp[2] == '1':
                source_tmp = '进口'
            elif group_tmp[2] == '2':
                source_tmp = '大矿长协'
            elif group_tmp[2] == '3':
                source_tmp = '大矿市场'
            elif group_tmp[2] == '4':
                source_tmp = '地方矿'
            dict_canshu['source'] = source_tmp
            dict_canshu['group'] = group_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
            # 下限
            coef_df1 = coef_df.copy()
            exec('true_value = ll_group_{} - 100'.format(group_tmp), locals(), ldict6)
            true_value = ldict6["true_value"]
            # print(true_value)
            exec('false_value = ll_group_{}'.format(group_tmp), locals(), ldict6)
            false_value = ldict6["false_value"]

            # print(false_value)
            def __cal_coef(x):
                if x.mark[0:3] == group_tmp:
                    rst = true_value
                else:
                    rst = false_value
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '性状来源比例下限'
            dict_canshu['step'] = '维度比例上下限'
            if group_tmp[0] == '1':
                var_tmp = '主焦'
            elif group_tmp[0] == '2':
                var_tmp = '肥煤'
            elif group_tmp[0] == '3':
                var_tmp = '1/3焦'
            elif group_tmp[0] == '4':
                var_tmp = '气煤'
            elif group_tmp[0] == '5':
                var_tmp = '瘦煤'
            dict_canshu['vvar'] = var_tmp
            if group_tmp[2] == '1':
                source_tmp = '进口'
            elif group_tmp[2] == '2':
                source_tmp = '大矿长协'
            elif group_tmp[2] == '3':
                source_tmp = '大矿市场'
            elif group_tmp[2] == '4':
                source_tmp = '地方矿'
            dict_canshu['source'] = source_tmp
            dict_canshu['group'] = group_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
        # 品名分配比例
        ldict7 = {}
        ldict8 = {}
        for mark_tmp in mark_list:
            # 上限
            coef_df1 = coef_df.copy()
            exec('true_value = 100 - ul_{}'.format(mark_tmp), locals(), ldict7)
            true_value = ldict7["true_value"]
            # print(true_value)
            exec('false_value = - ul_{}'.format(mark_tmp), locals(), ldict7)
            false_value = ldict7["false_value"]

            # print(false_value)
            def __cal_coef(x):
                if x.mark == mark_tmp:
                    rst = true_value
                else:
                    rst = false_value
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '品种比例上限'
            if mark_tmp[0] == '1':
                buzhou_tmp = '主焦'
            elif mark_tmp[0] == '2':
                buzhou_tmp = '肥煤'
            elif mark_tmp[0] == '3':
                buzhou_tmp = '1/3焦'
            elif mark_tmp[0] == '4':
                buzhou_tmp = '气煤'
            elif mark_tmp[0] == '5':
                buzhou_tmp = '瘦煤'
            dict_canshu['step'] = buzhou_tmp
            dict_canshu['mark'] = mark_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
            # 下限
            coef_df1 = coef_df.copy()
            exec('true_value = ll_{} - 100'.format(mark_tmp), locals(), ldict8)
            true_value = ldict8["true_value"]
            # print(true_value)
            exec('false_value = ll_{}'.format(mark_tmp), locals(), ldict8)
            false_value = ldict8["false_value"]

            # print(false_value)
            def __cal_coef(x):
                if x.mark == mark_tmp:
                    rst = true_value
                else:
                    rst = false_value
                return rst

            coef_df1['coef'] = coef_df1.apply(lambda x: __cal_coef(x), axis=1)
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                exec("y{}[0, index] = row['coef']".format(j_start))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '品种比例下限'
            if mark_tmp[0] == '1':
                buzhou_tmp = '主焦'
            elif mark_tmp[0] == '2':
                buzhou_tmp = '肥煤'
            elif mark_tmp[0] == '3':
                buzhou_tmp = '1/3焦'
            elif mark_tmp[0] == '4':
                buzhou_tmp = '气煤'
            elif mark_tmp[0] == '5':
                buzhou_tmp = '瘦煤'
            dict_canshu['step'] = buzhou_tmp
            dict_canshu['mark'] = mark_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
        # 煤质
        meizhi_list = ['hui', 'huifa', 'liu']
        big_var_tmp = 'lianjiao'
        for meizhi_tmp in meizhi_list:
            coef_df1 = coef_df.copy()
            # 上限
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                mark_tmp = row['mark']
                exec("y{}[0, index] = {}_{} - {}_{}_ul".format(j_start, meizhi_tmp, mark_tmp, big_var_tmp,
                                                               meizhi_tmp))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '上限'
            dict_canshu['step'] = '煤质上下限'
            dict_canshu['meizhi'] = meizhi_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1
            # 下限
            exec("y{} = array([[0]*array_len],dtype=float)".format(j_start))
            for index, row in coef_df1.iterrows():
                mark_tmp = row['mark']
                exec("y{}[0, index] = {}_{}_ll - {}_{}".format(j_start, big_var_tmp, meizhi_tmp, meizhi_tmp,
                                                               mark_tmp))
            exec("m{} =  array([0])".format(j_start))
            dict_canshu['index'] = j_start
            dict_canshu['coef_name'] = '下限'
            dict_canshu['step'] = '煤质上下限'
            dict_canshu['meizhi'] = meizhi_tmp
            new_row = pd.Series(dict_canshu)
            canshu_df = canshu_df.append(new_row, ignore_index=True)
            dict_canshu = {}
            j_start = j_start + 1

        # 拼接
        ldict0 = {}
        y_str = 'y0'
        # yyy = (y0,y0)
        for j in range(1, j_start):
            y_str = y_str + ',' + 'y' + str(j)
        # exec('yyy =({})'.format(y_str))
        exec('yyy =({})'.format(y_str), locals(), ldict0)
        yyy = ldict0["yyy"]
        Y = np.concatenate(yyy, axis=0)

        m_str = 'm0'
        # mmm = (m0,m0)
        for j in range(1, j_start):
            m_str = m_str + ',' + 'm' + str(j)
        # exec('mmm =({})'.format(m_str))
        exec('mmm =({})'.format(m_str), locals(), ldict0)
        mmm = ldict0["mmm"]
        M = np.concatenate(mmm, axis=0)
        # print('finish')
        # 计划
        data_mark_plan = data_mark[(data_mark['PLAN_WT'] > 0)]
        data_mark_plan = data_mark_plan.reset_index(drop=True)
        # mark_plan_list = data_mark_plan['mark'].to_list()
        v = ['PROD_CODE']
        df4 = pd.merge(data_mark_plan, data_meizhi_1, on=v, how='left')
        df4['UNIT_PRICE'] = 1
        df4['TOTAL_PRICE'] = df4['UNIT_PRICE'] * df4['PLAN_WT']
        df4['TOTAL_VV'] = df4['huifa'] * df4['PLAN_WT']
        plan_peihemei_total_price = df4['TOTAL_PRICE'].sum()
        plan_peihemei_unit_price = plan_peihemei_total_price / peihemei_wt
        plan_peihemei_huifa = df4['TOTAL_VV'].sum() / peihemei_wt
        plan_lilunchengjiaolv = 97 - plan_peihemei_huifa * 5 / 6 + chengjiaolv_constant
        plan_jiaotanchanliang = peihemei_wt * plan_lilunchengjiaolv / 100
        plan_COGfashengliang = (9.37 * plan_peihemei_huifa + 66.7) * peihemei_wt / 24 / 30 + cogfasheng_constant
        # 调整
        # 价格
        # cc假定价格为1
        cc = array([[1] * array_len], dtype=float)
        # 挥发分
        vv_str = ''
        ldict9 = {}
        for i in range(0, len(mark_list)):
            mark_tmp = mark_list[i]
            if i == 0:
                exec("vv_str = vv_str + 'huifa_{}'".format(mark_tmp), locals(), ldict9)
                vv_str = ldict9["vv_str"]
            else:
                exec("vv_str = vv_str + ',' + 'huifa_{}'".format(mark_tmp), locals(), ldict9)
                vv_str = ldict9["vv_str"]
                # print(vv_str)
        exec("vv = array([{}])".format(vv_str), locals(), ldict0)
        vv = ldict0["vv"]

        # 判断函数
        def judge_solution_exist(e1, f1):
            """
            传入e1，f1进行线性规划求解，判断是否有可行域
            """
            # cujiaolv = 0.115
            # yejinjiaolv = 0.835
            # waigoujiao_yejinjiaolv = 0.8
            # waigoujiao_cujiaolv = 0.2
            # COGfadian = 1.9
            # fadianmeihao = 420
            x = cp.Variable(array_len)
            peihemei_total_price = cc @ x
            peihemei_unit_price = peihemei_total_price / peihemei_wt
            peihemei_huifa = (vv @ x) / peihemei_wt
            lilunchengjiaolv = 97 - peihemei_huifa * 5 / 6 + chengjiaolv_constant
            jiaotanchanliang = peihemei_wt * lilunchengjiaolv / 100
            COGfashengliang = (9.37 * peihemei_huifa + 66.7) * peihemei_wt / 24 / 30 + cogfasheng_constant
            delta_jiaotanchanliang = jiaotanchanliang - plan_jiaotanchanliang

            delta_culiaoliang = delta_jiaotanchanliang * cujiaolv
            delta_waigoujiao = delta_jiaotanchanliang * yejinjiaolv / waigoujiao_yejinjiaolv
            delta_cujiaocaigou = delta_waigoujiao * waigoujiao_cujiaolv - delta_culiaoliang
            delta_waigoujiao_cost = -delta_waigoujiao * price_waigoujiaotan
            delta_cujiaocaigou_cost = delta_cujiaocaigou * price_waigoujiaofen
            # delta_peihemei_cost = peihemei_total_price - plan_peihemei_total_price
            delta_peihemei_cost = (peihemei_unit_price - plan_peihemei_unit_price) * ganmeiliang

            delta_COGfashengliang = (plan_COGfashengliang - COGfashengliang) * 30 * 24
            # COG_DEST焦炉煤气去向
            # 发电；补充焦炉煤气

            if cog_dest != '发电':
                delta_COG_cost = delta_COGfashengliang * price_cog
            # elif cog_dest=='发电':
            else:
                delta_COG_fadian = delta_COGfashengliang * COGfadian
                tianranqifadian = delta_COGfashengliang / 2
                fadianmeifadian = delta_COG_fadian * fadianmeihao / 100
                waigoudian = delta_COG_fadian
                # 都是delta_COGfashengliang的倍数，比较倍数取最小的
                tianranqi_coef = 0.5 * price_tianranqi
                fadianmei_coef = COGfadian * fadianmeihao / 100 * price_tianranqi / 10000
                waigoudian_coef = COGfadian * price_waigoudian
                delta_COG_cost1 = tianranqifadian * price_tianranqi
                delta_COG_cost2 = fadianmeifadian * price_fadianmei / 10000
                delta_COG_cost3 = waigoudian * price_waigoudian
                # delta_COG_cost = min(delta_COG_cost1, delta_COG_cost2, delta_COG_cost3)
                # # 发电三种代替方式，取最小的作为目标函数
                if tianranqi_coef <= fadianmei_coef and tianranqi_coef <= waigoudian_coef:
                    delta_COG_cost = delta_COG_cost1
                elif fadianmei_coef <= tianranqi_coef and fadianmei_coef <= waigoudian_coef:
                    delta_COG_cost = delta_COG_cost2
                elif waigoudian_coef <= tianranqi_coef and waigoudian_coef <= fadianmei_coef:
                    delta_COG_cost = delta_COG_cost3
            total_cost = delta_peihemei_cost + delta_waigoujiao_cost + delta_cujiaocaigou_cost + delta_COG_cost

            obj = cp.Minimize(total_cost)
            cons = [e1 @ x <= f1, e2 @ x == f2, x >= 0]
            prob = cp.Problem(obj, cons)
            prob.solve(solver='GLPK_MI', verbose=True)
            # print("最优初始值为:", prob.value)
            # print("最优初始解为：\n", x.value)
            success = 0
            if x.value is None:
                success = 0
            else:
                success = 1
            # print(success)
            return success

        success = judge_solution_exist(e1=Y, f1=M)
        yy = y0
        mm = m0
        listb = []
        if success == 1:
            message = '约束条件合理，存在最优解'
            result_list = []
            # print(message)
        else:
            message = '约束条件不合理，不存在最优解'
            # print(message)
            # print('无解，开始循环找需要修改的约束条件')
            for i in range(1, j_start):
                yi = Y[[i]]
                mi = M[[i]]
                Yy = np.concatenate((yy, yi), axis=0)
                Mm = np.concatenate((mm, mi), axis=0)
                success = judge_solution_exist(e1=Yy, f1=Mm)
                if success == 1:
                    yy = Yy
                    mm = Mm
                else:
                    yy = yy
                    mm = mm
                    # print(i)
                    listb.append(i)
                    # print(listb)
            df2 = canshu_df.iloc[listb]
            # print('需要修改的约束条件参数为')
            # print(df2)
        # df2 = canshu_df

            df2 = df2.reset_index(drop=True)
            data_mark_tmp = data_mark[['VAR', 'SOURCE', 'PROD_DSCR', 'mark']]
            data_mark_tmp.rename(columns={'VAR': 'VAR_TMP'}, inplace=True)
            data_mark_tmp.rename(columns={'SOURCE': 'SOURCE_TMP'}, inplace=True)
            data_mark_tmp.rename(columns={'PROD_DSCR': 'PROD_DSCR_TMP'}, inplace=True)

            v = ['mark']
            df2 = pd.merge(df2, data_mark_tmp, on=v, how='left')

            # var_df_tmp = var_df[['group', 'VAR', 'SOURCE']]
            # var_df_tmp.rename(columns={'SOURCE': 'SOURCE_TMP_group'}, inplace=True)
            # var_df_tmp.rename(columns={'VAR': 'VAR_TMP_group'}, inplace=True)
            # v = ['group']
            # df2 = pd.merge(df2, var_df_tmp, on=v, how='left')

            def __cal_coef(x):
                if x.step == '煤质上下限':
                    ind = ''
                    if x.meizhi == 'hui':
                        ind = '灰分'
                    elif x.meizhi == 'huifa':
                        ind = '挥发分'
                    elif x.meizhi == 'liu':
                        ind = '硫分'
                    rst = '炼焦煤' + '_' + ind + '_' + str(x.coef_name)
                elif x.coef_name == '品种比例上限' or x.coef_name == '品种比例下限':
                    rst = str(x.VAR_TMP) + '_' + str(x.SOURCE_TMP) + '_' + str(x.PROD_DSCR_TMP) + '_' + str(x.coef_name)
                elif x.coef_name == '性状比例上限' or x.coef_name == '性状比例下限':
                    rst = str(x.vvar) + '_' + str(x.coef_name)
                elif x.coef_name == '来源比例上限' or x.coef_name == '来源比例下限':
                    rst = str(x.source) + '_' + str(x.coef_name)
                elif x.coef_name == '性状来源比例上限' or x.coef_name == '性状来源比例下限':
                    rst = str(x.vvar) + '_' + str(x.source) + '_' + str(x.coef_name)
                elif x.coef_name == '资源量':
                    rst = str(x.VAR_TMP) + '_' + str(x.SOURCE_TMP) + '_' + str(x.PROD_DSCR_TMP) + '_' + str(x.coef_name)
                elif x.coef_name == '合并可供资源量':
                    rst = '第' + str(x.merge_flag) + '组' + '_' + str(x.coef_name)
                else:
                    rst = x.coef_name
                return rst

            df2['new_coef'] = df2.apply(lambda x: __cal_coef(x), axis=1)

            def __cal_coef_col_name(x):
                if x.step == '煤质上下限':
                    if x.meizhi == 'hui':
                        rst = 'ASH'
                    elif x.meizhi == 'huifa':
                        rst = 'COKE_VM'
                    elif x.meizhi == 'liu':
                        rst = 'S'
                elif x.coef_name == '品种比例上限':
                    rst = 'MAX_VALUE'
                elif x.coef_name == '品种比例下限':
                    rst = 'MIN_VALUE'
                elif x.coef_name == '性状比例上限':
                    rst = 'MAX_VALUE'
                elif x.coef_name == '性状比例下限':
                    rst = 'MIN_VALUE'
                elif x.coef_name == '来源比例上限':
                    rst = 'MAX_VALUE'
                elif x.coef_name == '来源比例下限':
                    rst = 'MIN_VALUE'
                elif x.coef_name == '性状来源比例上限':
                    rst = 'MAX_VALUE'
                elif x.coef_name == '性状来源比例下限':
                    rst = 'MIN_VALUE'
                elif x.coef_name == '资源量':
                    rst = 'RESOURCE_WT'
                elif x.coef_name == '合并可供资源量':
                    rst = 'INV_WT'
                return rst

            df2['new_coef_col_name'] = df2.apply(lambda x: __cal_coef_col_name(x), axis=1)

            def __cal_coef_index_name(x):
                if x.coef_name == '上限':
                    rst = '上限'
                elif x.coef_name == '下限':
                    rst = '下限'
                elif x.coef_name == '品种比例上限' or x.coef_name == '品种比例下限':
                    rst = x.PROD_DSCR_TMP
                elif x.coef_name == '性状比例上限' or x.coef_name == '性状比例下限':
                    rst = x.vvar
                elif x.coef_name == '来源比例上限' or x.coef_name == '来源比例下限':
                    rst = x.source
                elif x.coef_name == '性状来源比例上限' or x.coef_name == '性状来源比例下限':
                    rst = str(x.vvar) + '_' + str(x.source)
                elif x.coef_name == '资源量':
                    rst = x.PROD_DSCR_TMP
                elif x.coef_name == '合并可供资源量':
                    rst = x.merge_flag
                return rst

            df2['new_coef_index_name'] = df2.apply(lambda x: __cal_coef_index_name(x), axis=1)
            df_out = pd.DataFrame(columns=['COEF_NAME', 'STEP_NAME', 'TABLE_NAME', 'INDEX_NUM', 'COL_NUM'])
            dict = {}
            message = message + '需要修改的约束条件参数为：'
            for index, row in df2.iterrows():
                dict['COEF_NAME'] = row['new_coef']
                message = message + row['new_coef'] + '；'
                dict['STEP_NAME'] = row['step']
                if row['coef_name'] == '性状比例上限' or row['coef_name'] == '性状比例下限':
                    dict['TABLE_NAME'] = '表1'
                elif row['coef_name'] == '来源比例上限' or row['coef_name'] == '来源比例下限':
                    dict['TABLE_NAME'] = '表2'
                elif row['coef_name'] == '性状来源比例上限' or row['coef_name'] == '性状来源比例下限':
                    dict['TABLE_NAME'] = '表3'
                elif row['coef_name'] == '品种比例上限' or row['coef_name'] == '品种比例下限':
                    dict['TABLE_NAME'] = '表1'
                elif row['coef_name'] == '资源量':
                    dict['TABLE_NAME'] = '表1'
                else:
                    dict['TABLE_NAME'] = ''
                if row['step'] == '煤质上下限':
                    # sql = ""
                    sql = " select BIG_VAR,FLAG,ASH,COKE_VM,S " \
                          " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_INDLIMIT " \
                          " where TMPL_NO ='%s' and DATA_TYPE='MX' order by REC_ID" % (tmpl_no)
                    data_tmp = pd.read_sql_query(sql, con=db_conn_mpp)
                    data_tmp.columns = data_tmp.columns.str.upper()
                    flag_tmp = row['coef_name']
                    ind_tmp = row['new_coef_col_name']
                    row_index = data_tmp.loc[(data_tmp['FLAG'] == flag_tmp)].index[0]
                    column_index = data_tmp.columns.get_loc(ind_tmp)
                    dict['INDEX_NUM'] = row_index
                    dict['COL_NUM'] = column_index
                elif row['coef_name'] == '性状比例上限' or row['coef_name'] == '性状比例下限':
                    # sql = ""
                    sql = " select VAR,MIN_VALUE,MAX_VALUE " \
                          " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_RATIO " \
                          " where TMPL_NO ='%s' and DATA_TYPE='MX' and FLAG='性状' order by REC_ID" % (tmpl_no)
                    data_tmp = pd.read_sql_query(sql, con=db_conn_mpp)
                    data_tmp.columns = data_tmp.columns.str.upper()
                    var_tmp = row['vvar']
                    ind_tmp = row['new_coef_col_name']
                    row_index = data_tmp.loc[data_tmp['VAR'] == var_tmp].index[0]  # 找到满足条件的第一行的行索引
                    column_index = data_tmp.columns.get_loc(ind_tmp)
                    dict['INDEX_NUM'] = row_index
                    dict['COL_NUM'] = column_index
                elif row['coef_name'] == '来源比例上限' or row['coef_name'] == '来源比例下限':
                    # sql = ""
                    sql = " select SOURCE,MIN_VALUE,MAX_VALUE " \
                          " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_RATIO " \
                          " where TMPL_NO ='%s' and DATA_TYPE='MX' and FLAG='来源' order by REC_ID" % (tmpl_no)
                    data_tmp = pd.read_sql_query(sql, con=db_conn_mpp)
                    data_tmp.columns = data_tmp.columns.str.upper()
                    source_tmp = row['source']
                    ind_tmp = row['new_coef_col_name']
                    row_index = data_tmp.loc[data_tmp['SOURCE'] == source_tmp].index[0]  # 找到满足条件的第一行的行索引
                    column_index = data_tmp.columns.get_loc(ind_tmp)
                    dict['INDEX_NUM'] = row_index
                    dict['COL_NUM'] = column_index
                elif row['coef_name'] == '性状来源比例上限' or row['coef_name'] == '性状来源比例下限':
                    # sql = ""
                    sql = " select VAR,SOURCE,MIN_VALUE,MAX_VALUE " \
                          " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_RATIO " \
                          " where TMPL_NO ='%s' and DATA_TYPE='MX' and FLAG='性状来源' order by REC_ID" % (tmpl_no)
                    data_tmp = pd.read_sql_query(sql, con=db_conn_mpp)
                    data_tmp.columns = data_tmp.columns.str.upper()
                    var_tmp = row['vvar']
                    source_tmp = row['source']
                    ind_tmp = row['new_coef_col_name']
                    row_index = data_tmp.loc[(data_tmp['VAR'] == var_tmp) & (data_tmp['SOURCE'] == source_tmp)].index[0]  # 找到满足条件的第一行的行索引
                    column_index = data_tmp.columns.get_loc(ind_tmp)
                    dict['INDEX_NUM'] = row_index
                    dict['COL_NUM'] = column_index
                elif row['coef_name'] == '品种比例上限' or row['coef_name'] == '品种比例下限' or row['参数名'] == '资源量':
                    var_tmp = row['step']
                    sql = " select t1.VAR,t1.CLASS,t1.SOURCE,t1.PROD_DSCR,t1.PROD_CODE,t1.MIN_VALUE,t1.MAX_VALUE,t2.ASH,t2.COKE_VM,t2.S " \
                          " from ( " \
                          " select REC_ID,VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE,MIN_VALUE,MAX_VALUE " \
                          " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_RATIO " \
                          " where TMPL_NO ='%s' and DATA_TYPE='MX' and FLAG='品名' and VAR='%s' " \
                          " ) as t1 left join ( " \
                          " select PROD_CODE,ASH,COKE_VM,S " \
                          " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_IND_INFO " \
                          " where TMPL_NO ='%s' and DATA_TYPE='MX' " \
                          " ) as t2 " \
                          " on t1.PROD_CODE=t2.PROD_CODE order by t1.REC_ID " % (tmpl_no, var_tmp, tmpl_no)
                    data_tmp = pd.read_sql_query(sql, con=db_conn_mpp)
                    data_tmp.columns = data_tmp.columns.str.upper()
                    prod_dscr_tmp = row['PROD_DSCR']
                    ind_tmp = row['new_coef_col_name']
                    row_index = data_tmp.loc[data_tmp['PROD_DSCR'] == prod_dscr_tmp].index[0]  # 找到满足条件的第一行的行索引
                    column_index = data_tmp.columns.get_loc(ind_tmp)
                    dict['INDEX_NUM'] = row_index
                    dict['COL_NUM'] = column_index + 1
                elif row['coef_name'] == '合并可供资源量':
                    # sql = ""
                    sql = " select VAR,CLASS,SOURCE,PROD_DSCR,PROD_CODE,UNIT_PRICE,INIT_INV,INV_WT,PLAN_WT,MERGE_FLAG " \
                          " from BG00MAZZAI.T_ADS_WH_YLMX_COKE_PRICE " \
                          " where TMPL_NO ='%s' and DATA_TYPE='MX' order by REC_ID" % (tmpl_no)
                    data_tmp = pd.read_sql_query(sql, con=db_conn_mpp)
                    data_tmp.columns = data_tmp.columns.str.upper()
                    merge_tag_tmp = row['merge_flag']
                    ind_tmp = row['new_coef_col_name']
                    row_index = data_tmp.loc[data_tmp['merge_flag'] == merge_tag_tmp].index[0]  # 找到满足条件的第一行的行索引
                    column_index = data_tmp.columns.get_loc(ind_tmp)
                    dict['INDEX_NUM'] = row_index
                    dict['COL_NUM'] = column_index
                new_row = pd.Series(dict)
                df_out = df_out.append(new_row, ignore_index=True)
            result_list = df_out.to_dict(orient='records')
        return message, result_list
